[Question] DB error handling and rollback

[Nick-Motion]

First of all, thank you for your work on this, and congratulations on the recent beta. This is a very impressive library.

TL;DR:

In testing, I've encountered issues around migration errors and DB corruption that are concerning. I'd like to better understand any possible mitigations.

Problem:

I created a faulty migration (e.g., createdAt: State.SQLite.text({ default: { sql: 'CURENT_TIMESTAMP' } })), which passed TypeScript validation but caused the following issues:

• An opaque error was thrown (LiveStore.SqliteError, ... code 14: unable to open database file). This breaks the persisted database.
• Despite the database error, the bad migration was still applied to the in-memory DB, resulting in a createdAt: 'CURENT_TIMESTAMP' field.
• After this migration, all future data changes failed to persist. It's unclear to me if they are still pushed in sync, as I haven't properly wired up push.
• My primary concern: There doesn't appear to be any built-in mechanism to roll back or fix migration mistakes. It seems the current approach under the auto-migration system has no way to revert to a previous state, and the recommended method for fixing issues in development (resetting the DB and event log) is not recommended for production environments.
• Additionally, it seems also possible to perma-break both the in-memory and persisted DBs, e.g. if my migration were createdAt: State.SQLite.text({ default: new Date() }) - though this fails typescript check, so this specific example is only relevant in dev. I mention it only because it heightens my concern around needing robust rollback/reset.

Questions

Would you be able to clarify if there are any existing solutions or recommendations for handling these situations? Or is this something you are hoping to address in future updates?

I'm curious why the default behavior if a migration fails is not to maintain the current DB state. I see in recreate-db.ts that at one point a tmpDb was used, presumably to this effect?

Finally, I'm curious if/how manual migration mode could address these issues. I couldn't find documentation on a recommended approach.

[IGassmann]

Hey @Nick-Motion! Thanks for reporting this issue.

I suspect your problem is the same as the one described in #256. Could you please provide a reproduction so I can confirm this?

I attempted to replicate the faulty migration you specified, but I didn't encounter the error you mentioned. I only receive that error after triggering four migrations.

[Nick-Motion]

Thanks @IGassmann for the insight - you may be correct. I do see the the bad migration applied to both stores after clearing OPFS.

This covers why I was seeing unable to open database file. Does #256 imply that only 6 migrations are possible before hitting errors?

I am wondering still what the recommended path is to fix a bad migration. Before hitting unable to open database file, both removing and updating the offending row (createdAt: State.SQLite.text({ default: { sql: 'CURRENT_TIMESTAMP' } })) appear to work - is one pattern preferable with auto migration?